--创建查询scores表的存储过程p1
CREATE PROCEDURE p1
AS SELECT * FROM scores
GO
--执行存储过程p1
EXECUTE p1
GO
--创建用于查询指定用户课程的成绩存储过程p2，参数为用户名和课程名
CREATE PROCEDURE p2
@u NVARCHAR(MAX),@c NVARCHAR(MAX)
AS BEGIN
SELECT uname,cname,score FROM scores INNER JOIN users ON scores.uid=users.uid INNER JOIN courses ON scores.cid=courses.cid WHERE uname=@u AND cname=@c
END
GO
--为p2传递参数执行该存储过程
EXECUTE p2 @u='彼得',@c='sql'
GO
--修改存储过程p2
ALTER PROCEDURE p2
@u NVARCHAR(MAX),@c NVARCHAR(MAX)
AS BEGIN
SELECT score FROM scores INNER JOIN users ON scores.uid=users.uid INNER JOIN courses ON scores.cid=courses.cid WHERE uname=@u AND cname=@c
END
GO
--为p2传递参数执行该存储过程
EXECUTE p2 @u='玛丽亚',@c='html'
GO
--创建一个查询指定用户名的总成绩的存储过程p3
CREATE PROCEDURE dbo.p3
@sc CURSOR VARYING OUTPUT,
--此游标参数是输出参数（OUTPUT参数不接受其他值的赋值，而是为其他变量赋值），不能是表内的数据类型
@u NVARCHAR(MAX)
AS BEGIN
SET @sc=CURSOR LOCAL SCROLL STATIC FOR SELECT uname AS '用户名',SUM(score) AS '用户总分' FROM users INNER JOIN scores ON users.uid=scores.uid GROUP BY uname HAVING(uname=@u)
OPEN @sc
END
GO
--声明游标变量并执行存储过程p3
DECLARE @vc CURSOR
EXECUTE dbo.p3 @sc=@vc OUTPUT,
/*当等式左侧是一个OUTPUT参数时，等式右侧的模块或命令字符串也必须由OUTPUT定义
此处OUTPUT的作用是将等式左侧的输出参数的值返回给右侧*/
@u='保罗'
DECLARE @n INT=0
WHILE @n<@@CURSOR_ROWS BEGIN
FETCH NEXT FROM @vc
SET @n+=1
END
CLOSE @vc
DEALLOCATE @vc
GO
--在创建或修改存储过程时使用WITH RECOMPILE表示不缓存此过程的查询计划，强制每次执行过程时都对该过程进行编译
/*在创建存储过程p4的同时编译该存储过程*/
CREATE PROCEDURE p4
@wildcard NVARCHAR(MAX)
WITH RECOMPILE AS BEGIN
SELECT * FROM users WHERE sex LIKE @wildcard
END
GO
EXECUTE p4 @wildcard='fe%'
GO
--在修改存储过程p4时重新编译该存储过程
ALTER PROCEDURE p4
@wc DATE,@wildcard2 DATE
WITH RECOMPILE AS BEGIN
SELECT * FROM users WHERE birth BETWEEN @wc AND @wildcard2
END
GO
EXECUTE p4 @wc='1987-01-01',@wildcard2='1987-03-01'
GO
--创建存储过程procedure5
CREATE PROCEDURE procedure5 @uid INT AS SELECT uname FROM users WHERE uid=@uid
GO
/*执行存储过程procedure5
在执行存储过程时使用WITH RECOMPILE重新编译存储过程*/
EXECUTE procedure5 @uid=5 WITH RECOMPILE--为参数@wildcard赋值
GO
--在执行系统存储过程（sp_recompile）时对指定存储过程进行重新编译
EXECUTE sp_recompile procedure5
GO
--删除存储过程
DROP PROCEDURE p1[,...n]
GO
